﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using motelmanagement.common;
using System.Data;
using System.Data.OleDb;
namespace motelmanagement.dao
{
    class CustomerDAO
    {
        SqlUtil sqlUtil = new SqlUtil();
        OleDbCommand cmd = null;
        private string strSQL = null;
        //tra ve tat ca record
        public DataTable getALLCustomer()
        {
            cmd = new OleDbCommand("SELECT * from Customer");
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        //tra ve 1 record
        public DataTable getCustomer(int customerID)
        {
            cmd = new OleDbCommand("SELECT * from Customer WHERE cus_id=@id");
            cmd.Parameters.Add("id", SqlDbType.Int).Value = customerID;
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        //them 1 record
        public void insertCustomer(string cusName, int cusIDNum, string cusADD, string cusPhone, string cusSex, string cusCountry, string cusNote)
        {
            strSQL = "INSERT INTO Customer(cus_name,cus_idnumber,cus_address,cus_phone,cus_sex,cus_country,cus_note)";
            strSQL += " VALUES(@name,@idnumber,@address,@phone,@sex,@country,@note)";
            cmd = new OleDbCommand(strSQL);
            cmd.Parameters.Add("name", SqlDbType.NText).Value = cusName;
            cmd.Parameters.Add("idnumber", SqlDbType.Int).Value = cusIDNum;
            cmd.Parameters.Add("address", SqlDbType.NText).Value = cusADD;
            cmd.Parameters.Add("phone", SqlDbType.Text).Value = cusPhone;
            cmd.Parameters.Add("sex", SqlDbType.NText).Value = cusSex;
            cmd.Parameters.Add("country", SqlDbType.NText).Value = cusCountry;
            cmd.Parameters.Add("note", SqlDbType.NText).Value = cusNote;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        //update 1 record
        public void updateCustomer(int cusID, string cusName, int cusIDNum, string cusADD, string cusPhone, string cusSex, string cusCountry, string cusNote)
        {
            strSQL = "UPDATE Customer SET cus_name=@name,cus_idnumber=@idnumber";
            strSQL += ",cus_address=@address,cus_phone=@phone,cus_sex=@sex,cus_country=@country,cus_note=@note";
            strSQL += " WHERE cus_id=@id";
            cmd = new OleDbCommand(strSQL);
            cmd.Parameters.Add("name", SqlDbType.NText).Value = cusName;
            cmd.Parameters.Add("idnumber", SqlDbType.Int).Value = cusIDNum;
            cmd.Parameters.Add("address", SqlDbType.NText).Value = cusADD;
            cmd.Parameters.Add("phone", SqlDbType.Text).Value = cusPhone;
            cmd.Parameters.Add("sex", SqlDbType.NText).Value = cusSex;
            cmd.Parameters.Add("country", SqlDbType.NText).Value = cusCountry;
            cmd.Parameters.Add("note", SqlDbType.NText).Value = cusNote;
            cmd.Parameters.Add("id", SqlDbType.Int).Value = cusID;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        //xoa 1 record
        public void deleteCustomer(int cusID)
        {
            string strSql = "DELETE FROM Customer WHERE cus_id = @id";
            OleDbCommand cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id", SqlDbType.Int).Value = cusID;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
    }
}
